﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using AlexandriaPressAdminObject;

namespace AlexandriaPressAdminDataAccessLayer
{
    public class AuthorApplicationDL
    {

        private static string myConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["BookConn1"].ConnectionString;
            }
        }


        public static List<ApplicantInfo> displayAll()
        {
         
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;
 
            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.StoredProcedure;
            comm.CommandText = "usp_Display_Author_Application";

            if (conn.State != ConnectionState.Open)
                conn.Open();

            SqlDataReader dtReader = comm.ExecuteReader(CommandBehavior.CloseConnection);

            List<ApplicantInfo> lstOfApplicant = new List<ApplicantInfo>();
            ApplicantInfo applicant = null;

            while (dtReader.Read())
            {
                applicant = new ApplicantInfo();
                applicant.ID = dtReader.GetInt32(dtReader.GetOrdinal("UserID"));
                applicant.ProfilePicture = "~/MasterPages/UserPages/" + dtReader.GetString(dtReader.GetOrdinal("ProfilePicture"));
                applicant.Email = dtReader.GetString(dtReader.GetOrdinal("Email"));
                applicant.Name = dtReader.GetString(dtReader.GetOrdinal("Full Name"));
                applicant.Date = dtReader.GetDateTime(dtReader.GetOrdinal("DateSubmitted"));
                lstOfApplicant.Add(applicant);
            }

            return lstOfApplicant;
        }

        public static int approveApplicant(List<ApplicantInfo> _applicantList)
        {

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;
      
            if (conn.State != ConnectionState.Open) conn.Open();

            SqlTransaction transaction = conn.BeginTransaction();
            int i = 0;
            try
            {
                foreach (ApplicantInfo applicant in _applicantList)
                {

                    SqlCommand approveApplicant = new SqlCommand();
                    approveApplicant.Connection = conn;
                    approveApplicant.CommandType = CommandType.StoredProcedure;
                    approveApplicant.CommandText = "usp_Approve_Author_Application";
                    approveApplicant.Transaction = transaction;
   
                    SqlParameter param1 = new SqlParameter();
                    param1.DbType = DbType.Int32;
                    param1.Value = applicant.ID;
                    param1.ParameterName = "@userId";
                    param1.Direction = ParameterDirection.Input;
                    approveApplicant.Parameters.Add(param1);

                    SqlParameter param2 = new SqlParameter();
                    param2.DbType = DbType.Int32;
                    param2.Value = applicant.adminID;
                    param2.ParameterName = "@adminId";
                    param2.Direction = ParameterDirection.Input;
                    approveApplicant.Parameters.Add(param2);

                    approveApplicant.ExecuteScalar();
                    i++;
                }

                transaction.Commit();

            }

            catch
            {
                transaction.Rollback();
                i = 0;
            }

            finally
            {
                conn.Close();


            }
            return i;
        }

        public static int denyApplicant(List<ApplicantInfo> _applicantList)
        {
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString = myConnectionString;

            if (conn.State != ConnectionState.Open) conn.Open();

            SqlTransaction transaction = conn.BeginTransaction();
            int i = 0;
            try
            {

                foreach (ApplicantInfo applicant in _applicantList)
                {
                    SqlCommand denyApplicant = new SqlCommand();
                    denyApplicant.Connection = conn;
                    denyApplicant.CommandType = CommandType.StoredProcedure;
                    denyApplicant.CommandText = "usp_Deny_Author_Application";
                    denyApplicant.Transaction = transaction;

                    SqlParameter param1 = new SqlParameter();
                    param1.DbType = DbType.Int32;
                    param1.Value = applicant.ID;
                    param1.ParameterName = "@userId";
                    param1.Direction = ParameterDirection.Input;
                    denyApplicant.Parameters.Add(param1);

                    SqlParameter param2 = new SqlParameter();
                    param2.DbType = DbType.Int32;
                    param2.Value = applicant.adminID;
                    param2.ParameterName = "@adminId";
                    param2.Direction = ParameterDirection.Input;
                    denyApplicant.Parameters.Add(param2);

                    int a = Convert.ToInt32(denyApplicant.ExecuteScalar());
                    i++;
                }

                transaction.Commit();
            }
            catch
            {
                transaction.Rollback();
            }

            finally
            {
                conn.Close();
            }

            return i;
        }
    }
}
